package script

// 查询本单位电话工单数据
static def telWFDeptList_1(Object parameter){
    def prefix = '''
        select 
          t1.PkId,t1.WfId,t2.FmContent,t1.GetDealPK AS telId,t2.WorkFormNO,t2.WsTopic FromTopic,t2.FromName,t2.FromTelA FromTel,
          '12345' SpareString,t3.FmAddress,t3.FromTime,t3.DCntAName,t3.DFmClaName,SBDeptName,BackDesc,t4.JudgeC,t1.SDeptName,t1.SeqName,
          t1.SortName,t1.DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit,BackTime,BackState AS BackState,BackRemark 
        FROM tWfSendBack t1 
        left join tWorkFormBase t2 on t1.WfId=t2.PkId and t2.IsDel = 0 
        left join tTelGetDeal t3 on t2.GetDealPK=t3.PkId and t3.IsDel = 0 
        left join tWfJudge t4 on t4.WfId = t1.WfId and t4.IsDel = 0 
        WHERE t1.SBDeptID=#{param1} AND t1.SendState IN(21,22,23,40) AND t1.IsDel=0 
    '''
    return telDynamicSql(prefix, parameter)
}

// 查询本单位电话工单数据
static def telWFDeptList_2(Object parameter){
    def prefix = '''
          select 
            t1.PkId,t1.WfId,t2.FmContent,t1.GetDealPK AS telId,t2.WorkFormNO,t2.WsTopic FromTopic,t2.FromName,t2.FromTelA FromTel,
            '12345' SpareString,t3.FmAddress,t3.FromTime,t3.DCntAName,t3.DFmClaName,SBDeptName,BackDesc,t4.JudgeC,t1.SDeptName,t1.SeqName,
            t1.SortName,t1.DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit,BackTime,BackState AS BackState,BackRemark,
            row_number() over(partition by t1.PkId order by t4.VisitTime desc) groupIndex 
          FROM tWfSendBack t1 
          left join tWorkFormBase t2 on t1.WfId=t2.PkId and t2.IsDel = 0 
          left join tTelGetDeal t3 on t2.GetDealPK=t3.PkId and t3.IsDel = 0 
          left join tWfJudge t4 on t4.WfId = t1.WfId and t4.IsDel = 0 
          WHERE t1.SBDeptID=#{param1} AND t1.SendState IN(21,22,23,40) AND t1.IsDel=0 
    '''
    return telDynamicSql(prefix, parameter)
}

// 查询本单位电话工单数据
static def telWFDeptList(Object parameter){
    def prefix = '''
          select 
            t1.PkId,t1.WfId,t2.FmContent,t1.GetDealPK AS telId,t2.WorkFormNO,t2.WsTopic FromTopic,t2.FromName,t2.FromTelA FromTel,
            '12345' SpareString,t3.FmAddress,t3.FromTime,t3.DCntAName,t3.DFmClaName,SBDeptName,BackDesc,t1.SDeptName,t1.SeqName,
            t1.SortName,t1.DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit,BackTime,BackState AS BackState,BackRemark
          FROM tWfSendBack t1 
          left join tWorkFormBase t2 on t1.WfId=t2.PkId and t2.IsDel = 0 
          left join tTelGetDeal t3 on t2.GetDealPK=t3.PkId and t3.IsDel = 0 
          WHERE t1.SBDeptID=#{param1} AND t1.SendState IN(21,22,23,40) AND t1.IsDel=0 
    '''
    return telDynamicSql(prefix, parameter)
}

// 查询本单位信件工单
static def mailWFDeptList_1(Object parameter){
    def prefix = '''
        select 
          t1.PkId,t1.WfId,t2.FmContent,t1.MailPK,t2.WorkFormNO,t2.FromTopic,t2.FromName,t2.FromTel,t3.SpareString,
          t3.FmAddress,t3.FromTime,t3.DCntAName,t3.DFmClaName,SBDeptName,BackDesc,t4.JudgeC,SDeptName,SeqName,SortName,
          DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit,BackTime,BackState,BackRemark 
        FROM mWfSendBack t1 
        left join mMailWorkForm t2 on t1.WfId=t2.PkId and t2.IsDel = 0 
        left join mMailGetDeal t3 on t2.OriginFK=t3.PkId and t3.IsDel = 0 
        left join mWfJudge t4 on t4.WfId = t1.WfId and t4.IsDel = 0 
        WHERE SBDeptID=#{param1} AND SendState IN(21,22,23,40) AND t1.IsDel=0 
    '''
    return mailDynamicSql(prefix, parameter)
}

// 查询本单位信件工单
static def mailWFDeptList_2(Object parameter){
    def prefix = '''
        select 
          PkId,WfId,FmContent,MailPK,WorkFormNO,FromTopic,FromName,FromTel,SpareString,FmAddress,FromTime,DCntAName,
          DFmClaName,SBDeptName,BackDesc,JudgeC,SDeptName,SeqName,SortName,DealName,BackLimit,BackTime,BackState,BackRemark 
        from (
          select 
            t1.PkId,t1.WfId,t2.FmContent,t1.MailPK,t2.WorkFormNO,t2.FromTopic,t2.FromName,t2.FromTel,t3.SpareString,
            t3.FmAddress,t3.FromTime,t3.DCntAName,t3.DFmClaName,SBDeptName,BackDesc,t4.JudgeC,SDeptName,SeqName,SortName,
            DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit,BackTime,BackState,BackRemark,
            row_number() over(partition by t1.PkId order by t4.VisitTime desc) group_index 
          FROM mWfSendBack t1 
          left join mMailWorkForm t2 on t1.WfId=t2.PkId and t2.IsDel = 0 
          left join mMailGetDeal t3 on t2.OriginFK=t3.PkId and t3.IsDel = 0 
          left join mWfJudge t4 on t4.WfId = t1.WfId and t4.IsDel = 0 
          WHERE SBDeptID=#{param1} AND SendState IN(21,22,23,40) AND t1.IsDel=0 
    '''
    def postfix = ''' ) res where res.group_index = 1 '''
    return mailDynamicSql(prefix, parameter) + postfix
}

// 查询本单位信件工单
static def mailWFDeptList(Object parameter){
    def prefix = '''
          select 
            t1.PkId,t1.WfId,t2.FmContent,t1.MailPK,t2.WorkFormNO,t2.FromTopic,t2.FromName,t2.FromTel,t3.SpareString,
            t3.FmAddress,t3.FromTime,t3.DCntAName,t3.DFmClaName,SBDeptName,BackDesc,SDeptName,SeqName,SortName,
            DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit,BackTime,BackState,BackRemark
          FROM mWfSendBack t1 
          left join mMailWorkForm t2 on t1.WfId=t2.PkId and t2.IsDel = 0 
          left join mMailGetDeal t3 on t2.OriginFK=t3.PkId and t3.IsDel = 0 
          WHERE SBDeptID=#{param1} AND SendState IN(21,22,23,40) AND t1.IsDel=0 
    '''
    return mailDynamicSql(prefix, parameter)
}

// 查询本单位电话工单总数
static def telWFDeptListTotal(Object parameter){
    def prefix = '''
        select count(0)  
        FROM tWfSendBack t1 
        left join tWorkFormBase t2 on t1.WfId=t2.PkId and t2.IsDel = 0 
        left join tTelGetDeal t3 on t2.GetDealPK=t3.PkId and t3.IsDel = 0 
        left join tWfJudge t4 on t4.WfId = t1.WfId and t4.IsDel = 0 
        WHERE t1.SBDeptID=#{param1} AND t1.SendState IN(21,22,23,40) AND t1.IsDel=0 
    '''
    return telDynamicSql(prefix, parameter)
}

// 查询本单位信件工单总数
static def mailWFDeptListTotal(Object parameter){
    def prefix = '''
        select count(0) 
        FROM mWfSendBack t1 
        left join mMailWorkForm t2 on t1.WfId=t2.PkId and t2.IsDel = 0 
        left join mMailGetDeal t3 on t2.OriginFK=t3.PkId and t3.IsDel = 0 
        left join mWfJudge t4 on t4.WfId = t1.WfId and t4.IsDel = 0 
        WHERE SBDeptID=#{param1} AND SendState IN(21,22,23,40) AND t1.IsDel=0 
    '''
    return mailDynamicSql(prefix, parameter)
}

// 本单位电话工单动态查询字段
private static def telDynamicSql(String sql, Object parameter){
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),t2.IptTime,120) >= #{beginTime} "
    if (parameter?.endTime)
        sql += " and convert(varchar(10),t2.IptTime,120) <= #{endTime} "
    if (parameter?.workFormNO)
        sql += " and t2.workFormNO = #{workFormNO} "
    if (parameter?.wsCode)
        sql += " and t2.WsCode = #{wsCode} "
    if(parameter?.dcntAcode)
        sql += " and t2.DCntACode = #{dcntAcode} "
    if(parameter?.fromTopic)
        sql += " and t2.WsTopic = #{fromTopic} "
    if(parameter?.sdeptName)
        sql += " and t1.sdeptName = #{sdeptName} "
    return sql
}

// 本单位信件工单动态查询字段
private static def mailDynamicSql(String sql, Object parameter){
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),t2.IptTime,120) >= #{beginTime} "
   if(parameter?.endTime)
        sql += " and convert(varchar(10),t2.IptTime,120) <= #{endTime} "
    if (parameter?.workFormNO)
        sql += " and WorkFormNO = #{workFormNO} "
    if (parameter?.wsCode)
        sql += " and WsCode = #{wsCode} "
    if(parameter?.spareString)
        sql += " and t3.SpareString = #{spareString} "
    if(parameter?.dcntAcode)
        sql += " and t2.DCntACode = #{dcntAcode} "
    if(parameter?.fromTopic)
        sql += " and FromTopic = #{fromTopic} "
    if(parameter?.sdeptName)
        sql += " and sdeptName = #{sdeptName}"
    return sql
}